package com.neusoft.weekreport.component.bizService.impl;

import com.neusoft.weekreport.component.entities.ImportExcelAllUserInfo;
import com.neusoft.weekreport.component.entities.SendExcelUserInfo;
import com.neusoft.weekreport.component.req.GetExportExcelReq;
import com.neusoft.weekreport.mapper.ExportExcelMapper;
import com.neusoft.weekreport.mapper.SendExcelMailMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import javax.swing.text.html.parser.Entity;
import java.text.DateFormat;
import java.util.*;

@Service
public class ExportMonthExcelService {

    @Resource
    ExportExcelMapper exprotExcelMapper;
    @Resource
    SendExcelMailMapper sendExcelMailMapper;

    //按月日报导出
    public void exportMonthInfo(GetExportExcelReq req, HttpServletResponse response) throws Exception {
        List<ImportExcelAllUserInfo> userInfos = null;

        //开始时间
        long startTime = System.currentTimeMillis();
        //判断是组还是部门
        if (("10001".equals(req.getGroupid()) || "20001".equals(req.getGroupid())) && !"f93ac2b4-001f-4847-972d-7e986e59f17a".equals(req.getId())) {
//            userInfos = exprotExcelMapper.selectByDept(req);
        } else if ("f93ac2b4-001f-4847-972d-7e986e59f17a".equals(req.getId()) || "6b543480-f400-4190-9219-e92766fd2c92".equals(req.getId())) {
            userInfos = exprotExcelMapper.selectAllMonth(req);
        } else {
//            userInfos = exprotExcelMapper.selectByGroup(req);
        }
        String  userName = "";
        String compareDate = "";

        List<SendExcelUserInfo> result = new ArrayList<>();
        SendExcelUserInfo userInfo = null;
        List workContent = new ArrayList<>();
        List projbctName = new ArrayList<>();
        List delivbrables = new ArrayList<>();
        List dictValue = new ArrayList<>();
        Double laborHour = null;
        Double a;
        for (int i = 0; i < userInfos.size(); i++) {

            if(compareDate.equals(userInfos.get(i).getDayId()) && userName.equals(userInfos.get(i).getUserName())){
                //合并Content

                a = userInfos.get(i).getLabHour();
                laborHour += a;
                workContent.add(userInfos.get(i).getWorkContent());
                projbctName.add(userInfos.get(i).getProjbctName());
                delivbrables.add(userInfos.get(i).getDelivbrables());
                dictValue.add(userInfos.get(i).getDictValue());
                userInfo.setLabHour(laborHour);
                userInfo.setWorkContent(workContent);
                userInfo.setProjbctName(projbctName);
                userInfo.setDelivbrables(delivbrables);
                userInfo.setDictValue(dictValue);
            } else {
                userInfo = new SendExcelUserInfo();
                workContent = new ArrayList<>();
                projbctName = new ArrayList<>();
                delivbrables = new ArrayList<>();
                dictValue = new ArrayList<>();

                    laborHour = userInfos.get(i).getLabHour();
                    workContent.add(userInfos.get(i).getWorkContent());
                    projbctName.add(userInfos.get(i).getProjbctName());
                    delivbrables.add(userInfos.get(i).getDelivbrables());
                    dictValue.add(userInfos.get(i).getDictValue());
                    userInfo.setLabHour(laborHour);
                    userInfo.setWorkContent(workContent);
                    userInfo.setProjbctName(projbctName);
                    userInfo.setDelivbrables(delivbrables);
                    userInfo.setDictValue(dictValue);

                //other value
                BeanUtils.copyProperties(userInfos.get(i), userInfo);
                result.add(userInfo);
                userName = userInfos.get(i).getUserName();
                compareDate = userInfos.get(i).getDayId();
            }
        }
        //生成Excel流文件返回给前端
        DateFormat df1 = DateFormat.getDateInstance();
        String fileName = "云医疗事业部-" + getDayListOfMonth().get(0).substring(0, 7) + "月日报汇总.xlsx";//文件名称
        response.setContentType("application/vnd.ms-excel;charset = utf-8");//将结果导出为Excel
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1"));//将文件作为附件下载
        XSSFWorkbook workbook = writeExcelInfo(result);
        workbook.write(response.getOutputStream());
        //结束时间
        long endTime = System.currentTimeMillis();
        //转换为秒
        float excTime = (float) (endTime - startTime) / 1000;
        //控制台打印时间
        System.out.println("执行时间：" + excTime + "s");

    }

    public XSSFWorkbook writeExcelInfo(List<SendExcelUserInfo> userInfos) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();//创建Excel工作簿对象
        XSSFSheet sheet = workbook.createSheet();//在工作簿中创建工作表对象
        sheet.setDisplayGridlines(false);//隐藏Excel网格线,默认值为true
        //设置列宽度
        sheet.setColumnWidth(0, 12 * 256);
        sheet.setColumnWidth(1, 8 * 256);
        sheet.setColumnWidth(2, 25 * 256);
        sheet.setColumnWidth(3, 21 * 256);
        sheet.setColumnWidth(4, 20 * 256);
        sheet.setColumnWidth(5, 10 * 256);
        sheet.setColumnWidth(6, 11 * 256);
        sheet.setColumnWidth(7, 24 * 256);
        sheet.setColumnWidth(8, 40 * 256);
        sheet.setColumnWidth(9, 30 * 256);
        sheet.setColumnWidth(10, 15 * 256);
        sheet.setColumnWidth(11, 8 * 256);
        workbook.setSheetName(0,"日报汇总");//设置工作表的名称
        int colNum = 0;
        //工作明细
        colNum = setWeekDetailExcel(workbook, sheet, colNum, userInfos);
        return workbook;
    }

    //循环填充工作明细内容
    public int setWeekDetailExcel(XSSFWorkbook workbook, XSSFSheet sheet, int colNum, List<SendExcelUserInfo> userInfos) {

        Row row02 = sheet.createRow(colNum);
        row02.setHeight((short) (27 * 20));
        Cell cell2 = row02.createCell(0);
        cell2.setCellStyle(setStyleWeekDetailColunm(workbook, 177, 160, 199));
        cell2.setCellValue("日期");
        Cell cell21 = row02.createCell(1);
        cell21.setCellStyle(setStyleWeekDetailColunm(workbook, 177, 160, 199));
        cell21.setCellValue("姓名");
        Cell cell22 = row02.createCell(2);
        cell22.setCellStyle(setStyleWeekDetailColunm(workbook, 177, 160, 199));
        cell22.setCellValue("邮件");
        Cell cell23 = row02.createCell(3);
        cell23.setCellStyle(setStyleWeekDetailColunm(workbook, 177, 160, 199));
        cell23.setCellValue("二级部门");
        Cell cell24 = row02.createCell(4);
        cell24.setCellStyle(setStyleWeekDetailColunm(workbook, 177, 160, 199));
        cell24.setCellValue("三级部门");
        Cell cell25 = row02.createCell(5);
        cell25.setCellStyle(setStyleWeekDetailColunm(workbook, 177, 160, 199));
        cell25.setCellValue("直接负责人");
        Cell cell26 = row02.createCell(6);
        cell26.setCellStyle(setStyleWeekDetailColunm(workbook, 177, 160, 199));
        cell26.setCellValue("编制");
        Cell cell28 = row02.createCell(7);
        cell28.setCellStyle(setStyleWeekDetailColunm(workbook, 255, 192, 0));
        cell28.setCellValue("项目" + "\r\n" + "（没有具体项目就空着）");
        Cell cell29 = row02.createCell(8);
        cell29.setCellStyle(setStyleWeekDetailColunm(workbook, 255, 192, 0));
        cell29.setCellValue("工作内容");
        Cell cell30 = row02.createCell(9);
        cell30.setCellStyle(setStyleWeekDetailColunm(workbook, 255, 192, 0));
        cell30.setCellValue("成果物");
        Cell cell31 = row02.createCell(10);
        cell31.setCellStyle(setStyleWeekDetailColunm(workbook, 255, 192, 0));
        cell31.setCellValue("支持方式");
        Cell cell32 = row02.createCell(11);
        cell32.setCellStyle(setStyleWeekDetailColunm(workbook, 255, 192, 0));
        cell32.setCellValue("工时" + "\r\n" + "（小时）");
        ++colNum;

        //开始时间
        long startTime = System.currentTimeMillis();
        //日报具体信息
        for (SendExcelUserInfo userInfo : userInfos) {
            Row row03 = sheet.createRow(colNum);
            Cell cell33 = row03.createCell(0);
            cell33.setCellStyle(setStyleWeekDetailContent(workbook));
            cell33.setCellValue(userInfo.getDayId());

            Cell cell34 = row03.createCell(1);
            cell34.setCellStyle(setStyleWeekDetailContent(workbook));
            cell34.setCellValue(userInfo.getUserName());

            Cell cell35 = row03.createCell(2);
            cell35.setCellStyle(setStyleWeekDetailContent(workbook));
            cell35.setCellValue(userInfo.getEmailAddress());

            Cell celli36 = row03.createCell(3);
            celli36.setCellStyle(setStyleWeekDetailContent(workbook));
            celli36.setCellValue(userInfo.getFristGroup());

            Cell celli37 = row03.createCell(4);
            celli37.setCellStyle(setStyleWeekDetailContent(workbook));
            celli37.setCellValue(userInfo.getGroupName());

            Cell celli38 = row03.createCell(5);
            celli38.setCellStyle(setStyleWeekDetailContent(workbook));
            celli38.setCellValue(userInfo.getDescription());

            Cell celli39 = row03.createCell(6);
            celli39.setCellStyle(setStyleWeekDetailContent(workbook));
            celli39.setCellValue(userInfo.getUserKind());

            Cell celli40 = row03.createCell(7);
            celli40.setCellStyle(setStyleDayDetailContent(workbook));
            String b = "";
            int bb = 1;
            Map map = new HashMap();
            for (int i = 0; i < userInfo.getProjbctName().size(); i++) {
                map.put(i, userInfo.getProjbctName().get(i));
            }
            deleteDuplicate2(map);
            Iterator<Map.Entry<String, String>> iterator = map.entrySet().iterator();
            while (iterator.hasNext()) {
                String value = iterator.next().getValue();
                String a = "";
                if (userInfo.getProjbctName().size() != bb) {
                    if (value.equals("") && value == "null") {
                        a = "";
                    } else if (!value.equals("") && value != "null") {
                        a = value + ";\r\n";
                    }
                } else {
                    if (value.equals("") && value == "null") {
                        a = "";
                    } else if (!value.equals("") && value != "null") {
                        a = value + ";";
                    }
                }
                b += a;
                bb++;
            }
            celli40.setCellValue(b);

            Cell celli41 = row03.createCell(8);
            celli41.setCellStyle(setStyleDayDetailContent(workbook));
            String e = "";
            int aa = 1;
            Map map2 = new HashMap();
            for (int i = 0; i < userInfo.getWorkContent().size(); i++) {
                map2.put(i, userInfo.getWorkContent().get(i));
            }
            deleteDuplicate2(map2);
            Iterator<Map.Entry<String, String>> iterator2 = map2.entrySet().iterator();
            while (iterator2.hasNext()) {
                String value = iterator2.next().getValue();
                String a = "";
                if (userInfo.getWorkContent().size() != aa) {
                    a = String.valueOf(aa) + "、" + value + ";\r\n";
                } else {
                    a = String.valueOf(aa) + "、" + value + ";";
                }
                e += a;
                aa++;
            }
            celli41.setCellValue(e);


            Cell celli42 = row03.createCell(9);
            String c = "";
            int cc = 1;
            Map map3 = new HashMap();
            for (int i = 0; i < userInfo.getDelivbrables().size(); i++) {
                map3.put(i, userInfo.getDelivbrables().get(i));
            }
            deleteDuplicate2(map3);
            Iterator<Map.Entry<String, String>> iterator3 = map3.entrySet().iterator();
            while (iterator3.hasNext()) {
                String a = "";
                String value = iterator3.next().getValue();
                if (userInfo.getDelivbrables().size() != cc) {
                    if (value.equals("") && value == "null") {
                        a = "";
                    } else if (!value.equals("") && value != "null") {
                        a = String.valueOf(cc) + "、" + value + ";\r\n";
                    }
                } else {
                    if (value.equals("") && value == "null") {
                        a = "";
                    } else if (!value.equals("") && value != "null") {
                        a = String.valueOf(cc) + "、" + value + ";";
                    }
                }
                c += a;
                cc++;
            }
            celli42.setCellStyle(setStyleDayDetailContent(workbook));
            celli42.setCellValue(c);

            Cell celli43 = row03.createCell(10);
            celli43.setCellStyle(setStyleDayDetailContent(workbook));
            String d = "";
            int dd = 1;
            Map map4 = new HashMap();
            for (int i = 0; i < userInfo.getDictValue().size(); i++) {
                map4.put(i, userInfo.getDictValue().get(i));
            }
            deleteDuplicate2(map4);
            Iterator<Map.Entry<String, String>> iterator4 = map4.entrySet().iterator();
            while (iterator4.hasNext()) {
                String value = iterator4.next().getValue();
                String aaa = "";
                if (userInfo.getDictValue().size() != dd) {
                    if (value.equals("") && value == "null") {
                        aaa = "";
                    } else if (!value.equals("") && value != "null") {
                        aaa = value + ";\r\n";
                    }
                } else {
                    if (value.equals("") && value == "null") {
                        aaa = "";
                    } else if (!value.equals("") && value != "null") {
                        aaa = value + ";";
                    }
                }
                d += aaa;
                dd++;
            }
            celli43.setCellValue(d);

            Cell celli44 = row03.createCell(11);
            celli44.setCellStyle(setStyleDayDetailContent(workbook));
            celli44.setCellValue(userInfo.getLabHour());
            ++colNum;
        }
        //结束时间
        long endTime = System.currentTimeMillis();
        //转换为秒
        float excTime = (float) (endTime - startTime) / 1000;
        //控制台打印时间
        System.out.println("拼接cell执行时间：" + excTime + "s");
        return colNum;
    }

    //设置日报明细表头样式
    public XSSFCellStyle setStyleWeekDetailColunm(XSSFWorkbook workbook, int a, int b, int c) {

        //设置单元格字体
        XSSFCellStyle cellStyle = workbook.createCellStyle();//用于设置单元格的显示样式

        XSSFColor xssfColor = new XSSFColor(new java.awt.Color(a, b, c));
        cellStyle.setFillForegroundColor(xssfColor);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中

        cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        XSSFFont font = workbook.createFont();//创建字体对象
        font.setFontHeightInPoints((short) 10);//设置字号大小
        font.setFontName("微软雅黑");//设置字体
        font.setBold(true);//设置粗体
        cellStyle.setFont(font);//将字体设置添加到单元格样式中。

        cellStyle.setWrapText(true);
        return cellStyle;
    }

    //设置日报明细内容样式
    public XSSFCellStyle setStyleWeekDetailContent(XSSFWorkbook workbook) {
        //设置单元格字体
        XSSFCellStyle cellStyle = workbook.createCellStyle();//用于设置单元格的显示样式
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setWrapText(true);
        cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        XSSFFont font = workbook.createFont();//创建字体对象
        font.setFontHeightInPoints((short) 10);//设置字号大小
        font.setFontName("微软雅黑");//设置字体
        cellStyle.setFont(font);//将字体设置添加到单元格样式中。
        cellStyle.setWrapText(true);
        return cellStyle;
    }

    //设置日报明细内容样式
    public XSSFCellStyle setStyleDayDetailContent(XSSFWorkbook workbook) {
        //设置单元格字体
        XSSFCellStyle cellStyle = workbook.createCellStyle();//用于设置单元格的显示样式
        cellStyle.setVerticalAlignment(VerticalAlignment.TOP);//顶端对齐
        cellStyle.setAlignment(HorizontalAlignment.LEFT);//左对齐
        cellStyle.setWrapText(true);
        cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

        XSSFFont font = workbook.createFont();//创建字体对象
        font.setFontHeightInPoints((short) 10);//设置字号大小
        font.setFontName("微软雅黑");//设置字体
        cellStyle.setFont(font);//将字体设置添加到单元格样式中。
        cellStyle.setWrapText(true);
        return cellStyle;
    }

    //获取当月所有天
    public static List<String> getDayListOfMonth() {
        List<String> list = new ArrayList<String>();
        Calendar aCalendar = Calendar.getInstance(Locale.CHINA);
        int year = aCalendar.get(Calendar.YEAR);//年份
        int month = aCalendar.get(Calendar.MONTH) + 1;//月份
        int day = aCalendar.getActualMaximum(Calendar.DATE);
        for (int i = 1; i <= day; i++) {
            String month2 = String.format("%02d", month);
            String day2 = String.format("%02d", i);
            String aDate = String.valueOf(year) + "-" + month2 + "-" + day2;
            list.add(aDate);
        }
        return list;
    }

    private static void deleteDuplicate2(Map<String, String> map) {
        if (map == null || map.size() == 0) {
            return;
        }
        Set<String> set = new HashSet<String>();
        for (Iterator<Map.Entry<String, String>> iterator = map.entrySet().iterator(); iterator.hasNext();) {
            Map.Entry<String, String> entry = iterator.next();
            if (set.contains(entry.getValue())) {
                iterator.remove();
                continue;
            } else {
                set.add(entry.getValue());
            }
        }
    }
}